from collections import defaultdict
from datetime import datetime

from flask import request, jsonify
from flask_restx import Namespace, Resource, fields
from flask_jwt_extended import jwt_required
from sqlalchemy import and_, func

# 导入 tb_alarm 模型
from app1.models import Zbfwb_RemedyBiangeng
from app1 import db
from app1 import api

from app1.customdate import CustomDate
from app1.utils import format_response

from app1.utils import format_response, format_response_page

# 创建 Flask-RESTx 命名空间
ns_remedy_biangeng = Namespace('nsremedybiangeng', description='remedy 变更')

# 定义 Flask-RESTx 模型
remedy_biangeng_model = ns_remedy_biangeng.model('RemedyBiangeng', {
    'TicketID': fields.String(description='工单编号', required=True),
    'Change_Request_Status': fields.Integer(description='变更请求状态'),
    'StatusDescription': fields.String(description='状态描述'),
    'RequestType': fields.Integer(description='请求类型'),
    'CHGName': fields.String(description='变更名称'),
    'Reason': fields.String(description='变更原因'),
    'ChangeType': fields.Integer(description='变更类型'),
    'ChangeType2': fields.String(description='变更类型2'),
    'EvnType': fields.Integer(description='事件类型'),
    'Batch': fields.String(description='批次'),
    'Categorization_Tier_4': fields.String(description='分类层次4'),
    'ChangeRange': fields.String(description='变更范围'),
    'StartDate': CustomDate(dt_format='str_time', description='开始日期'),
    'EndDate': CustomDate(dt_format='str_time', description='结束日期'),
    'ImpactMonitor': fields.Integer(description='影响监控'),
    'Last_Name': fields.String(description='最后修改人'),
    'Department': fields.String(description='部门'),
    'ASCHG': fields.String(description='变更分配'),
    'ASGRP': fields.String(description='变更分配组'),
    'RecTime': CustomDate(dt_format='str_time', description='记录时间'),
    'SendHx': fields.String(description='发送历史'),
})

# 定义 Flask-RESTx 模型
remedy_biangeng_model_id = ns_remedy_biangeng.model('RemedyBiangeng', {
    'id': fields.Integer(description='序号'),
    'TicketID': fields.String(description='工单编号', required=True),
    'Change_Request_Status': fields.Integer(description='变更请求状态'),
    'StatusDescription': fields.String(description='状态描述'),
    'RequestType': fields.Integer(description='请求类型'),
    'CHGName': fields.String(description='变更名称'),
    'Reason': fields.String(description='变更原因'),
    'ChangeType': fields.Integer(description='变更类型'),
    'ChangeType2': fields.String(description='变更类型2'),
    'EvnType': fields.Integer(description='事件类型'),
    'Batch': fields.String(description='批次'),
    'Categorization_Tier_4': fields.String(description='分类层次4'),
    'ChangeRange': fields.String(description='变更范围'),
    'StartDate': CustomDate(dt_format='str_time', description='开始日期'),
    'EndDate': CustomDate(dt_format='str_time', description='结束日期'),
    'ImpactMonitor': fields.Integer(description='影响监控'),
    'Last_Name': fields.String(description='最后修改人'),
    'Department': fields.String(description='部门'),
    'ASCHG': fields.String(description='变更分配'),
    'ASGRP': fields.String(description='变更分配组'),
    'RecTime': CustomDate(dt_format='str_time', description='记录时间'),
    'SendHx': fields.String(description='发送历史'),
})

# 定义外层的响应模型
response_biangeng_model = ns_remedy_biangeng.model('Response_Biangeng', {
    'message': fields.Nested(ns_remedy_biangeng.model('Message_Biangeng', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'count': fields.Integer(description='查询结果返回数据条数'),
        'data': fields.List(fields.Nested(remedy_biangeng_model))
    }))
})

remedy_biangeng_multi_query_model = ns_remedy_biangeng.model(
    'remedy_biangeng_multi_query',
    {
        'TicketID': fields.String(description='工单编号', required=True, default=""),
        'Change_Request_Status': fields.String(description='变更请求状态', default=""),
        'StatusDescription': fields.String(description='状态描述', default=""),
        'RequestType': fields.String(description='请求类型', default=""),
        'CHGName': fields.String(description='变更名称', default=""),  # 模糊查询
        'Reason': fields.String(description='变更原因', default=""),  # 模糊查询
        'ChangeType': fields.String(description='变更类型', default=""),
        'ChangeType2': fields.String(description='变更类型2', default=""),
        'EvnType': fields.String(description='事件类型', default=""),
        'Batch': fields.String(description='批次', default=""),
        'Categorization_Tier_4': fields.String(description='分类层次4', default=""),
        'ChangeRange': fields.String(description='变更范围', default=""),
        # 'StartDate': fields.DateTime(description='开始日期'),
        # 'EndDate': fields.DateTime(description='结束日期'),
        'ImpactMonitor': fields.String(description='影响监控', default=""),
        'Last_Name': fields.String(description='最后修改人', default=""),
        'Department': fields.String(description='部门', default=""),
        'ASCHG': fields.String(description='变更分配', default=""),
        'ASGRP': fields.String(description='变更分配组', default=""),
        'StartDate_start_date': fields.String(description='开始日期起始',
                                              default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'StartDate_end_date': fields.String(description='开始日期结束',
                                            default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'EndDate_start_date': fields.String(description='结束日期开始',
                                            default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'EndDate_end_date': fields.String(description='结束日期结束',
                                          default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
    }
)

remedy_biangeng_multi_query_group_model = ns_remedy_biangeng.model(
    'remedy_biangeng_multi_query_group',
    {
        'TicketID': fields.List(fields.String, description='工单编号', default=[]),
        'Change_Request_Status': fields.List(fields.String, description='变更请求状态', default=[]),
        'StatusDescription': fields.List(fields.String, description='状态描述', default=[]),
        'RequestType': fields.List(fields.String, description='请求类型', default=[]),
        'CHGName': fields.List(fields.String, description='变更名称', default=[]),  # 模糊查询
        'Reason': fields.List(fields.String, description='变更原因', default=[]),  # 模糊查询
        'ChangeType': fields.List(fields.String, description='变更类型', default=[]),
        'ChangeType2': fields.List(fields.String, description='变更类型2', default=[]),
        'EvnType': fields.List(fields.String, description='事件类型', default=[]),
        'Batch': fields.List(fields.String, description='批次', default=[]),
        'Categorization_Tier_4': fields.List(fields.String, description='分类层次4', default=[]),
        'ChangeRange': fields.List(fields.String, description='变更范围', default=[]),
        # 'StartDate': fields.DateTime(description='开始日期'),
        # 'EndDate': fields.DateTime(description='结束日期'),
        'ImpactMonitor': fields.List(fields.String, description='影响监控', default=[]),
        'Last_Name': fields.List(fields.String, description='最后修改人', default=[]),
        'Department': fields.List(fields.String, description='部门', default=[]),
        'ASCHG': fields.List(fields.String, description='变更分配', default=[]),
        'ASGRP': fields.List(fields.String, description='变更分配组', default=[]),
        'StartDate_start_date': fields.String(description='开始日期起始',
                                              default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'StartDate_end_date': fields.String(description='开始日期结束',
                                            default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'EndDate_start_date': fields.String(description='结束日期开始',
                                            default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'EndDate_end_date': fields.String(description='结束日期结束',
                                          default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
    }
)

remedy_biangeng_multi_query_count_model = ns_remedy_biangeng.model(
    'remedy_biangeng_multi_query_count',
    {
        'TicketID': fields.String(description='工单编号', required=True, default=""),
        'Change_Request_Status': fields.String(description='变更请求状态', default=""),
        'StatusDescription': fields.String(description='状态描述', default=""),
        'RequestType': fields.String(description='请求类型', default=""),
        'CHGName': fields.String(description='变更名称', default=""),  # 模糊查询
        'Reason': fields.String(description='变更原因', default=""),  # 模糊查询
        'ChangeType': fields.String(description='变更类型', default=""),
        'ChangeType2': fields.String(description='变更类型2', default=""),
        'EvnType': fields.String(description='事件类型', default=""),
        'Batch': fields.String(description='批次', default=""),
        'Categorization_Tier_4': fields.String(description='分类层次4', default=""),
        'ChangeRange': fields.String(description='变更范围', default=""),
        # 'StartDate': fields.DateTime(description='开始日期'),
        # 'EndDate': fields.DateTime(description='结束日期'),
        'ImpactMonitor': fields.String(description='影响监控', default=""),
        'Last_Name': fields.String(description='最后修改人', default=""),
        'Department': fields.String(description='部门', default=""),
        'ASCHG': fields.String(description='变更分配', default=""),
        'ASGRP': fields.String(description='变更分配组', default=""),
        'StartDate_start_date': fields.String(description='开始日期起始',
                                              default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'StartDate_end_date': fields.String(description='开始日期结束',
                                            default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'EndDate_start_date': fields.String(description='结束日期开始',
                                            default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'EndDate_end_date': fields.String(description='结束日期结束',
                                          default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'count_now_time': fields.String(description='设定当前查询时间',
                                        default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
    }
)

# 定义 Flask-RESTx 模型
remedy_biangeng_count_model = ns_remedy_biangeng.model('RemedyBiangeng_count', {
    'biangeng_count_all': fields.Integer(description='变更总数'),
    'biangeng_count_now': fields.Integer(description='当前变更数'),
})

# 定义外层的响应模型
response_biangeng_count_model = ns_remedy_biangeng.model('Response_Biangeng_count', {
    'message': fields.Nested(ns_remedy_biangeng.model('Message_Biangeng_count', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'count': fields.Integer(description='查询结果返回数据条数'),
        'data': fields.List(fields.Nested(remedy_biangeng_count_model))
    }))
})

# input 查询指定栏位的取值数量
remedy_biangeng_filed_type_model = ns_remedy_biangeng.model(
    'remedy_biangeng_multi_filed_type',
    {
        'filedtype': fields.String(description='栏位,取值ChangeRange 应用名称  ASCHG 变更人', required=True,
                                   default="ChangeRange")
    }
)

# output
# 定义 Flask-RESTx 模型
remedy_biangeng_field_type_model = ns_remedy_biangeng.model('RemedyBiangeng_count', {
    'biangeng_field_type': fields.String(description='栏位取值'),
    'biangeng_field_count': fields.Integer(description='栏位取值数量'),
})

# 定义外层的响应模型
response_biangeng_field_type_model = ns_remedy_biangeng.model('Response_Biangeng_field_type', {
    'message': fields.Nested(ns_remedy_biangeng.model('Message_Biangeng_field_type', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'count': fields.Integer(description='查询结果返回数据条数'),
        'data': fields.List(fields.Nested(remedy_biangeng_field_type_model))
    }))
})

# 分页查询 input
remedy_biangeng_multi_query_page_model = ns_remedy_biangeng.model(
    'remedy_biangeng_multi_query_page',
    {
        'TicketID': fields.String(description='工单编号', required=True, default=""),
        'Change_Request_Status': fields.String(description='变更请求状态', default=""),
        'StatusDescription': fields.String(description='状态描述', default=""),
        'RequestType': fields.String(description='请求类型', default=""),
        'CHGName': fields.String(description='变更名称', default=""),  # 模糊查询
        'Reason': fields.String(description='变更原因', default=""),  # 模糊查询
        'ChangeType': fields.String(description='变更类型', default=""),
        'ChangeType2': fields.String(description='变更类型2', default=""),
        'EvnType': fields.String(description='事件类型', default=""),
        'Batch': fields.String(description='批次', default=""),
        'Categorization_Tier_4': fields.String(description='分类层次4', default=""),
        'ChangeRange': fields.String(description='变更范围', default=""),
        # 'StartDate': fields.DateTime(description='开始日期'),
        # 'EndDate': fields.DateTime(description='结束日期'),
        'ImpactMonitor': fields.String(description='影响监控', default=""),
        'Last_Name': fields.String(description='最后修改人', default=""),
        'Department': fields.String(description='部门', default=""),
        'ASCHG': fields.String(description='变更分配', default=""),
        'ASGRP': fields.String(description='变更分配组', default=""),
        'StartDate_start_date': fields.String(description='开始日期起始',
                                              default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'StartDate_end_date': fields.String(description='开始日期结束',
                                            default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'EndDate_start_date': fields.String(description='结束日期开始',
                                            default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'EndDate_end_date': fields.String(description='结束日期结束',
                                          default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'pageNo': fields.Integer(required=False, description='start page', default=1),
        'pageSize': fields.Integer(required=False, description='per page', default=20),
        'max_page': fields.Integer(required=False, description='max page', default=50),
    }
)

# 分页查询 output

remedy_biangeng_page_data_model = ns_remedy_biangeng.model('remedy_biangeng_pagedata', {
    'has_next': fields.Boolean(),  # has_next: 是否还有下一页
    'has_prev': fields.Boolean(),  # has_prev: 是否还有上一页
    'data': fields.List(fields.Nested(remedy_biangeng_model)),  # items: 返回当前页的所有内容
    'next': fields.List(fields.Nested(remedy_biangeng_model)),  # next(error_out=False): 返回下一页的Pagination对象
    'prev': fields.List(fields.Nested(remedy_biangeng_model)),  # prev(error_out=False): 返回上一页的Pagination对象
    'pageNo': fields.Integer(),  # page: 当前页的页码(从1开始)
    'totalPage': fields.Integer(),  # pages: 总页数
    'pageSize': fields.Integer(),  # per_page: 每页显示的数量
    'prev_num': fields.Integer(),  # prev_num: 上一页页码数
    'next_num': fields.Integer(),  # next_num: 下一页页码数
    # query: 返回
    'totalCount': fields.Integer()  # total: 查询返回的记录总数
})

# 定义外层的响应翻页返回
response_biangeng_page_data_model = ns_remedy_biangeng.model('Response_biangeng_page_data', {
    "message": fields.String(description='翻页查询结果'),
    "result": fields.Nested(remedy_biangeng_page_data_model),
    "status": fields.Integer(description='返回状态', default=200),  # 查询返回状态,
    "timestamp": fields.String(description='时间戳', default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),  # 时间戳
})

# 分页查询 output

remedy_biangeng_page_data_id_model = ns_remedy_biangeng.model('remedy_biangeng_page_data_id', {
    'has_next': fields.Boolean(),  # has_next: 是否还有下一页
    'has_prev': fields.Boolean(),  # has_prev: 是否还有上一页
    'data': fields.List(fields.Nested(remedy_biangeng_model_id)),  # items: 返回当前页的所有内容
    'next': fields.List(fields.Nested(remedy_biangeng_model_id)),  # next(error_out=False): 返回下一页的Pagination对象
    'prev': fields.List(fields.Nested(remedy_biangeng_model_id)),  # prev(error_out=False): 返回上一页的Pagination对象
    'pageNo': fields.Integer(),  # page: 当前页的页码(从1开始)
    'totalPage': fields.Integer(),  # pages: 总页数
    'pageSize': fields.Integer(),  # per_page: 每页显示的数量
    'prev_num': fields.Integer(),  # prev_num: 上一页页码数
    'next_num': fields.Integer(),  # next_num: 下一页页码数
    # query: 返回
    'totalCount': fields.Integer()  # total: 查询返回的记录总数
})

# 定义外层的响应翻页返回
response_biangeng_page_data_id_model = ns_remedy_biangeng.model('Response_biangeng_page_data_id', {
    "message": fields.String(description='翻页查询结果'),
    "result": fields.Nested(remedy_biangeng_page_data_id_model),
    "status": fields.Integer(description='返回状态', default=200),  # 查询返回状态,
    "timestamp": fields.String(description='时间戳', default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),  # 时间戳
})

# 分页多值查询 input
remedy_biangeng_multi_query_page_group_model = ns_remedy_biangeng.model(
    'remedy_biangeng_multi_query_page_group',
    {
        'TicketID': fields.List(fields.String, description='工单编号', required=True, default=[]),
        'Change_Request_Status': fields.List(fields.String, description='变更请求状态', default=[]),
        'StatusDescription': fields.List(fields.String, description='状态描述', default=[]),
        'RequestType': fields.List(fields.String, description='请求类型', default=[]),
        'CHGName': fields.List(fields.String, description='变更名称', default=[]),  # 模糊查询
        'Reason': fields.List(fields.String, description='变更原因', default=[]),  # 模糊查询
        'ChangeType': fields.List(fields.String, description='变更类型', default=[]),
        'ChangeType2': fields.List(fields.String, description='变更类型2', default=[]),
        'EvnType': fields.List(fields.String, description='事件类型', default=[]),
        'Batch': fields.List(fields.String, description='批次', default=[]),
        'Categorization_Tier_4': fields.List(fields.String, description='分类层次4', default=[]),
        'ChangeRange': fields.List(fields.String, description='变更范围', default=[]),
        'ImpactMonitor': fields.List(fields.String, description='影响监控', default=[]),
        'Last_Name': fields.List(fields.String, description='最后修改人', default=[]),
        'Department': fields.List(fields.String, description='部门', default=[]),
        'ASCHG': fields.List(fields.String, description='变更分配', default=[]),
        'ASGRP': fields.List(fields.String, description='变更分配组', default=[]),
        'StartDate_start_date': fields.String(description='开始日期起始',
                                              default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'StartDate_end_date': fields.String(description='开始日期结束',
                                            default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'EndDate_start_date': fields.String(description='结束日期开始',
                                            default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'EndDate_end_date': fields.String(description='结束日期结束',
                                          default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'pageNo': fields.Integer(required=False, description='start page', default=1),
        'pageSize': fields.Integer(required=False, description='per page', default=20),
        'max_page': fields.Integer(required=False, description='max page', default=50),
    }
)

# 按应用展示图表输出
# output
# 定义外层的响应模型
response_biangeng_app_count_model = ns_remedy_biangeng.model('Response_Biangeng_app_count', {
    'message': fields.Nested(ns_remedy_biangeng.model('Message_Biangeng_app_count', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'count': fields.Integer(description='查询结果返回数据条数'),
        'data': fields.List(fields.List(fields.Raw))  # data 是一个列表的列表
    }))
})

# 按人名展示图表输出
# output
# 定义外层的响应模型
response_biangeng_staff_count_model = ns_remedy_biangeng.model('Response_Biangeng_staff_count', {
    'message': fields.Nested(ns_remedy_biangeng.model('Message_Biangeng_staff_count', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'count': fields.Integer(description='查询结果返回数据条数'),
        'data': fields.List(fields.List(fields.Raw))  # data 是一个列表的列表
    }))
})


@ns_remedy_biangeng.route('/getbymultiquerylist')
class RemedyMultiQbiangengMapResource(Resource):

    @ns_remedy_biangeng.expect(remedy_biangeng_multi_query_model)
    # @jwt_required()
    @ns_remedy_biangeng.marshal_with(response_biangeng_model)
    def post(self):
        """根据给定条件查询变更并给出明细"""
        try:
            remedybiangengdata = api.payload

            # 提取查询条件
            query_filters = []
            # 多条件查询逻辑
            if remedybiangengdata.get('TicketID') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.TicketID == remedybiangengdata['TicketID'])

            if remedybiangengdata.get('Change_Request_Status') not in [None, '']:
                try:
                    query_filters.append(
                        Zbfwb_RemedyBiangeng.Change_Request_Status == int(remedybiangengdata['Change_Request_Status']))
                except ValueError:
                    pass  # 如果不能转换为整数，则忽略此条件

            if remedybiangengdata.get('StatusDescription') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.StatusDescription == remedybiangengdata['StatusDescription'])

            if remedybiangengdata.get('RequestType') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.RequestType == int(remedybiangengdata['RequestType']))
                except ValueError:
                    pass

            if remedybiangengdata.get('CHGName') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.CHGName.ilike(f"%{remedybiangengdata['CHGName']}%"))

            if remedybiangengdata.get('Reason') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Reason.ilike(f"%{remedybiangengdata['Reason']}%"))

            if remedybiangengdata.get('ChangeType') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.ChangeType == int(remedybiangengdata['ChangeType']))
                except ValueError:
                    pass

            if remedybiangengdata.get('ChangeType2') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ChangeType2 == remedybiangengdata['ChangeType2'])

            if remedybiangengdata.get('EvnType') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.EvnType == int(remedybiangengdata['EvnType']))
                except ValueError:
                    pass

            if remedybiangengdata.get('Batch') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Batch == remedybiangengdata['Batch'])

            if remedybiangengdata.get('Categorization_Tier_4') not in [None, '']:
                query_filters.append(
                    Zbfwb_RemedyBiangeng.Categorization_Tier_4 == remedybiangengdata['Categorization_Tier_4'])

            if remedybiangengdata.get('ChangeRange') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ChangeRange == remedybiangengdata['ChangeRange'])

            if remedybiangengdata.get('ImpactMonitor') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.ImpactMonitor == int(remedybiangengdata['ImpactMonitor']))
                except ValueError:
                    pass

            if remedybiangengdata.get('Last_Name') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Last_Name == remedybiangengdata['Last_Name'])

            if remedybiangengdata.get('Department') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Department == remedybiangengdata['Department'])

            if remedybiangengdata.get('ASCHG') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ASCHG == remedybiangengdata['ASCHG'])

            if remedybiangengdata.get('ASGRP') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ASGRP == remedybiangengdata['ASGRP'])

            # 处理 StartDate 和 EndDate 范围查询
            if remedybiangengdata.get('StartDate_start_date') not in [None, ''] and remedybiangengdata.get(
                    'StartDate_end_date') not in [None, '']:
                start_date_start = datetime.strptime(remedybiangengdata['StartDate_start_date'], '%Y-%m-%d %H:%M:%S')
                start_date_end = datetime.strptime(remedybiangengdata['StartDate_end_date'], '%Y-%m-%d %H:%M:%S')
                query_filters.append(Zbfwb_RemedyBiangeng.StartDate.between(start_date_start, start_date_end))

            if remedybiangengdata.get('EndDate_start_date') not in [None, ''] and remedybiangengdata.get(
                    'EndDate_end_date') not in [None, '']:
                end_date_start = datetime.strptime(remedybiangengdata['EndDate_start_date'], '%Y-%m-%d %H:%M:%S')
                end_date_end = datetime.strptime(remedybiangengdata['EndDate_end_date'], '%Y-%m-%d %H:%M:%S')
                query_filters.append(Zbfwb_RemedyBiangeng.EndDate.between(end_date_start, end_date_end))

                # 执行查询
                results = Zbfwb_RemedyBiangeng.query.filter(*query_filters).all()

                # 准备响应数据
                response_data = {
                    "message": {
                        "result": 1,  # 查询成功
                        "result_text": "查询成功",
                        "count": len(results),
                        "data": [result.to_dict() for result in results]
                    }
                }

                return response_data, 200

        except Exception as e:
            # 捕获异常并准备异常响应数据
            response_data = {
                "message": {
                    "result": 0,  # 查询异常
                    "result_text": f"查询异常: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }

            # 返回封装好的异常响应模型，状态码201
            return response_data, 201


# 根据给定多条件查询变更并给出明细
@ns_remedy_biangeng.route('/getbyMultiGroupquerylist')
class RemedyMultiGroupQbiangengMapResource(Resource):

    @ns_remedy_biangeng.expect(remedy_biangeng_multi_query_group_model)
    @ns_remedy_biangeng.marshal_with(response_biangeng_model)
    def post(self):
        """根据给定多条件查询变更并给出明细"""
        try:
            remedybiangengdata = api.payload

            # 提取查询条件
            query_filters = []

            # 处理多值查询逻辑
            def add_list_filter(field_name, model_field, exact_match=True):
                values = remedybiangengdata.get(field_name, [])
                if values:
                    if exact_match:
                        query_filters.append(model_field.in_(values))
                    else:
                        for value in values:
                            query_filters.append(model_field.ilike(f"%{value}%"))

            add_list_filter('TicketID', Zbfwb_RemedyBiangeng.TicketID)
            add_list_filter('Change_Request_Status', Zbfwb_RemedyBiangeng.Change_Request_Status)
            add_list_filter('StatusDescription', Zbfwb_RemedyBiangeng.StatusDescription)
            add_list_filter('RequestType', Zbfwb_RemedyBiangeng.RequestType)
            add_list_filter('CHGName', Zbfwb_RemedyBiangeng.CHGName, exact_match=False)
            add_list_filter('Reason', Zbfwb_RemedyBiangeng.Reason, exact_match=False)
            add_list_filter('ChangeType', Zbfwb_RemedyBiangeng.ChangeType)
            add_list_filter('ChangeType2', Zbfwb_RemedyBiangeng.ChangeType2)
            add_list_filter('EvnType', Zbfwb_RemedyBiangeng.EvnType)
            add_list_filter('Batch', Zbfwb_RemedyBiangeng.Batch)
            add_list_filter('Categorization_Tier_4', Zbfwb_RemedyBiangeng.Categorization_Tier_4)
            add_list_filter('ChangeRange', Zbfwb_RemedyBiangeng.ChangeRange)
            add_list_filter('ImpactMonitor', Zbfwb_RemedyBiangeng.ImpactMonitor)
            add_list_filter('Last_Name', Zbfwb_RemedyBiangeng.Last_Name)
            add_list_filter('Department', Zbfwb_RemedyBiangeng.Department)
            add_list_filter('ASCHG', Zbfwb_RemedyBiangeng.ASCHG)
            add_list_filter('ASGRP', Zbfwb_RemedyBiangeng.ASGRP)

            # 处理日期范围查询
            if remedybiangengdata.get('StartDate_start_date') and remedybiangengdata.get('StartDate_end_date'):
                try:
                    start_date_start = datetime.strptime(remedybiangengdata['StartDate_start_date'], '%Y-%m-%d %H:%M:%S')
                    start_date_end = datetime.strptime(remedybiangengdata['StartDate_end_date'], '%Y-%m-%d %H:%M:%S')
                    query_filters.append(Zbfwb_RemedyBiangeng.StartDate.between(start_date_start, start_date_end))
                except ValueError:
                    pass

            if remedybiangengdata.get('EndDate_start_date') and remedybiangengdata.get('EndDate_end_date'):
                try:
                    end_date_start = datetime.strptime(remedybiangengdata['EndDate_start_date'], '%Y-%m-%d %H:%M:%S')
                    end_date_end = datetime.strptime(remedybiangengdata['EndDate_end_date'], '%Y-%m-%d %H:%M:%S')
                    query_filters.append(Zbfwb_RemedyBiangeng.EndDate.between(end_date_start, end_date_end))
                except ValueError:
                    pass

            # 执行查询
            results = Zbfwb_RemedyBiangeng.query.filter(*query_filters).all()

            # 准备响应数据
            response_data = {
                "message": {
                    "result": 1,  # 查询成功
                    "result_text": "查询成功",
                    "count": len(results),
                    "data": [result.to_dict() for result in results]
                }
            }

            return response_data, 200

        except Exception as e:
            # 捕获异常并准备异常响应数据
            response_data = {
                "message": {
                    "result": 0,  # 查询异常
                    "result_text": f"查询异常: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }

            # 返回封装好的异常响应模型，状态码201
            return response_data, 201


# 查询返回总数和当前变更数
#  1返回一个总数， 2，根据count_now_time ，如果落在 StartDate 和 EndDate 区间， 则算出多少数据落在这区间
@ns_remedy_biangeng.route('/getbymultiquerycount')
class RemedyMultiQbgcountMapResource(Resource):

    @ns_remedy_biangeng.expect(remedy_biangeng_multi_query_count_model)
    # @jwt_required()
    @ns_remedy_biangeng.marshal_with(response_biangeng_count_model)
    def post(self):
        """根据给定条件查询所有的变更和当前变更数"""
        try:
            remedybiangengdata = api.payload

            # 提取查询条件
            query_filters = []
            # 多条件查询逻辑
            if remedybiangengdata.get('TicketID') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.TicketID == remedybiangengdata['TicketID'])

            if remedybiangengdata.get('Change_Request_Status') not in [None, '']:
                try:
                    query_filters.append(
                        Zbfwb_RemedyBiangeng.Change_Request_Status == int(remedybiangengdata['Change_Request_Status']))
                except ValueError:
                    pass  # 如果不能转换为整数，则忽略此条件

            if remedybiangengdata.get('StatusDescription') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.StatusDescription == remedybiangengdata['StatusDescription'])

            if remedybiangengdata.get('RequestType') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.RequestType == int(remedybiangengdata['RequestType']))
                except ValueError:
                    pass

            if remedybiangengdata.get('CHGName') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.CHGName.ilike(f"%{remedybiangengdata['CHGName']}%"))

            if remedybiangengdata.get('Reason') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Reason.ilike(f"%{remedybiangengdata['Reason']}%"))

            if remedybiangengdata.get('ChangeType') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.ChangeType == int(remedybiangengdata['ChangeType']))
                except ValueError:
                    pass

            if remedybiangengdata.get('ChangeType2') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ChangeType2 == remedybiangengdata['ChangeType2'])

            if remedybiangengdata.get('EvnType') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.EvnType == int(remedybiangengdata['EvnType']))
                except ValueError:
                    pass

            if remedybiangengdata.get('Batch') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Batch == remedybiangengdata['Batch'])

            if remedybiangengdata.get('Categorization_Tier_4') not in [None, '']:
                query_filters.append(
                    Zbfwb_RemedyBiangeng.Categorization_Tier_4 == remedybiangengdata['Categorization_Tier_4'])

            if remedybiangengdata.get('ChangeRange') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ChangeRange == remedybiangengdata['ChangeRange'])

            if remedybiangengdata.get('ImpactMonitor') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.ImpactMonitor == int(remedybiangengdata['ImpactMonitor']))
                except ValueError:
                    pass

            if remedybiangengdata.get('Last_Name') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Last_Name == remedybiangengdata['Last_Name'])

            if remedybiangengdata.get('Department') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Department == remedybiangengdata['Department'])

            if remedybiangengdata.get('ASCHG') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ASCHG == remedybiangengdata['ASCHG'])

            if remedybiangengdata.get('ASGRP') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ASGRP == remedybiangengdata['ASGRP'])

            # 处理 StartDate 和 EndDate 范围查询
            if remedybiangengdata.get('StartDate_start_date') not in [None, ''] and remedybiangengdata.get(
                    'StartDate_end_date') not in [None, '']:
                start_date_start = datetime.strptime(remedybiangengdata['StartDate_start_date'], '%Y-%m-%d %H:%M:%S')
                start_date_end = datetime.strptime(remedybiangengdata['StartDate_end_date'], '%Y-%m-%d %H:%M:%S')
                query_filters.append(Zbfwb_RemedyBiangeng.StartDate.between(start_date_start, start_date_end))

            if remedybiangengdata.get('EndDate_start_date') not in [None, ''] and remedybiangengdata.get(
                    'EndDate_end_date') not in [None, '']:
                end_date_start = datetime.strptime(remedybiangengdata['EndDate_start_date'], '%Y-%m-%d %H:%M:%S')
                end_date_end = datetime.strptime(remedybiangengdata['EndDate_end_date'], '%Y-%m-%d %H:%M:%S')
                query_filters.append(Zbfwb_RemedyBiangeng.EndDate.between(end_date_start, end_date_end))

            # 执行查询
            # 执行查询，计算总数
            query = Zbfwb_RemedyBiangeng.query.filter(*query_filters)
            biangeng_count_all = query.count()

            # # 计算当前变更数（根据 count_now_time 判断记录是否在 StartDate 和 EndDate 范围内）
            # count_now_time_str = remedybiangengdata.get('count_now_time',
            #                                             str(datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
            # count_now_time = datetime.strptime(count_now_time_str, '%Y-%m-%d %H:%M:%S')
            #
            # # 在 StartDate 和 EndDate 区间内的数据
            # biangeng_count_now = query.filter(Zbfwb_RemedyBiangeng.StartDate <= count_now_time,
            #                                   Zbfwb_RemedyBiangeng.EndDate >= count_now_time).count()

            # 获取查询时间字符串
            count_now_time_str = remedybiangengdata.get('count_now_time', str(datetime.now().strftime('%Y-%m-%d')))
            # 去掉时间部分，只保留日期
            count_now_time_str = count_now_time_str.split(' ')[0]  # 只取日期部分

            # 将日期字符串转换为 datetime 对象（只保留日期）
            count_now_time = datetime.strptime(count_now_time_str, '%Y-%m-%d').date()

            # 在 StartDate 和 EndDate 区间内的数据，比较时仅考虑日期，不考虑时间
            biangeng_count_now = query.filter(
                func.date(Zbfwb_RemedyBiangeng.StartDate) <= count_now_time,  # 仅比较日期部分
                func.date(Zbfwb_RemedyBiangeng.EndDate) >= count_now_time  # 仅比较日期部分
            ).count()

            # 准备响应数据
            response_data = {
                "message": {
                    "result": 1,  # 查询成功
                    "result_text": "查询成功",
                    "count": 2,  # 这里表示总数和当前变更数的两个数据返回
                    "data": [
                        {
                            "biangeng_count_all": biangeng_count_all,
                            "biangeng_count_now": biangeng_count_now
                        }
                    ]
                }
            }

            # 返回查询结果，状态码200
            return response_data, 200

        except Exception as e:
            # 捕获异常并准备异常响应数据
            response_data = {
                "message": {
                    "result": 0,  # 查询异常
                    "result_text": f"查询异常: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }

            # 返回封装好的异常响应模型，状态码201
            return response_data, 201


# 栏位合法性检查：在尝试查询之前，先使用 hasattr 检查 filedtype 是否是 Zbfwb_RemedyBiangeng 模型中的有效字段。
# 如果字段不存在，则返回错误消息，避免 SQL 查询时的错误。
#
# 查询逻辑：通过 SQLAlchemy 的 func.count 和 group_by 方法，对指定栏位进行分组统计，计算出每个取值的出现次数。
#
# 异常处理：在 except 块中捕获 SQLAlchemyError 异常，确保任何数据库异常都能被捕获并返回合适的错误信息。
#
# 返回格式：最终返回的响应数据符合 response_biangeng_field_type_model 的结构，包含每个栏位取值和对应的计数。

# 查询每个栏位取值的数量，返回取值和对应的数量
@ns_remedy_biangeng.route('/getbymultiqueryfiledtype')
class RemedyMultiQbgfieldtypeMapResource(Resource):

    @ns_remedy_biangeng.expect(remedy_biangeng_filed_type_model)
    # @jwt_required()
    @ns_remedy_biangeng.marshal_with(response_biangeng_field_type_model)
    def post(self):
        """根据输入的栏位名字统计该栏位的取值"""
        try:

            remedybiangengdata = api.payload

            # 主要处理逻辑
            filedtype = remedybiangengdata.get('filedtype')

            # 检查 filedtype 是否为空
            if not filedtype:
                return {
                    "message": {
                        "result": 0,  # 查询失败
                        "result_text": "查询失败: 未提供栏位名称",
                        "count": 0,
                        "data": []
                    }
                }, 400

            # 目前主要查询 ChangeRange 应用名称  ASCHG 变更人
            # 检查 filedtype 是否为 Zbfwb_RemedyBiangeng 的有效栏位
            if not hasattr(Zbfwb_RemedyBiangeng, filedtype):
                return {
                    "message": {
                        "result": 0,  # 查询失败
                        "result_text": f"查询失败: 栏位 '{filedtype}' 不存在",
                        "count": 0,
                        "data": []
                    }
                }, 400

            # 使用 SQLAlchemy 查询并统计指定栏位的取值数量
            result = (
                db.session.query(getattr(Zbfwb_RemedyBiangeng, filedtype).label("biangeng_field_type"),
                                 func.count(getattr(Zbfwb_RemedyBiangeng, filedtype)).label("biangeng_field_count"))
                .group_by(getattr(Zbfwb_RemedyBiangeng, filedtype))
                .all()
            )

            # 组织返回数据
            data = [
                {
                    "biangeng_field_type": row.biangeng_field_type,
                    "biangeng_field_count": row.biangeng_field_count
                }
                for row in result
            ]

            # 构造成功响应
            response_data = {
                "message": {
                    "result": 1,  # 查询成功
                    "result_text": "查询成功",
                    "count": len(data),
                    "data": data
                }
            }

            # 返回封装好的成功响应数据，状态码200
            return response_data, 200

        except Exception as e:
            # 捕获异常并准备异常响应数据
            response_data = {
                "message": {
                    "result": 0,  # 查询异常
                    "result_text": f"查询异常: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }

            # 返回封装好的异常响应模型，状态码201
            return response_data, 201


# 分页查询

@ns_remedy_biangeng.route('/remedybiangengpage')
class RemedyGongdanPageResource(Resource):
    @ns_remedy_biangeng.marshal_with(response_biangeng_page_data_model)
    @ns_remedy_biangeng.expect(remedy_biangeng_multi_query_page_model)
    def post(self):
        """分页获取变更单列表"""
        remedybiangengdata = api.payload

        try:
            # 获取分页请求信息
            # page = pagedata['page']
            page = remedybiangengdata['pageNo']
            if page < 1:
                page = 1

            # per_page = pagedata['per_page']
            per_page = remedybiangengdata['pageSize']
            if per_page < 1:
                per_page = 10

            max_per_page = remedybiangengdata['max_page']
            error_out = False

            # ----------------------------------------------------------------------------------

            # 提取查询条件
            query_filters = []
            # 多条件查询逻辑
            if remedybiangengdata.get('TicketID') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.TicketID == remedybiangengdata['TicketID'])

            if remedybiangengdata.get('Change_Request_Status') not in [None, '']:
                try:
                    query_filters.append(
                        Zbfwb_RemedyBiangeng.Change_Request_Status == int(remedybiangengdata['Change_Request_Status']))
                except ValueError:
                    pass  # 如果不能转换为整数，则忽略此条件

            if remedybiangengdata.get('StatusDescription') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.StatusDescription == remedybiangengdata['StatusDescription'])

            if remedybiangengdata.get('RequestType') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.RequestType == int(remedybiangengdata['RequestType']))
                except ValueError:
                    pass

            if remedybiangengdata.get('CHGName') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.CHGName.ilike(f"%{remedybiangengdata['CHGName']}%"))

            if remedybiangengdata.get('Reason') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Reason.ilike(f"%{remedybiangengdata['Reason']}%"))

            if remedybiangengdata.get('ChangeType') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.ChangeType == int(remedybiangengdata['ChangeType']))
                except ValueError:
                    pass

            if remedybiangengdata.get('ChangeType2') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ChangeType2 == remedybiangengdata['ChangeType2'])

            if remedybiangengdata.get('EvnType') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.EvnType == int(remedybiangengdata['EvnType']))
                except ValueError:
                    pass

            if remedybiangengdata.get('Batch') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Batch == remedybiangengdata['Batch'])

            if remedybiangengdata.get('Categorization_Tier_4') not in [None, '']:
                query_filters.append(
                    Zbfwb_RemedyBiangeng.Categorization_Tier_4 == remedybiangengdata['Categorization_Tier_4'])

            if remedybiangengdata.get('ChangeRange') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ChangeRange == remedybiangengdata['ChangeRange'])

            if remedybiangengdata.get('ImpactMonitor') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.ImpactMonitor == int(remedybiangengdata['ImpactMonitor']))
                except ValueError:
                    pass

            if remedybiangengdata.get('Last_Name') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Last_Name == remedybiangengdata['Last_Name'])

            if remedybiangengdata.get('Department') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Department == remedybiangengdata['Department'])

            if remedybiangengdata.get('ASCHG') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ASCHG == remedybiangengdata['ASCHG'])

            if remedybiangengdata.get('ASGRP') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ASGRP == remedybiangengdata['ASGRP'])

            # 处理 StartDate 和 EndDate 范围查询
            if remedybiangengdata.get('StartDate_start_date') not in [None, ''] and remedybiangengdata.get(
                    'StartDate_end_date') not in [None, '']:
                start_date_start = datetime.strptime(remedybiangengdata['StartDate_start_date'], '%Y-%m-%d %H:%M:%S')
                start_date_end = datetime.strptime(remedybiangengdata['StartDate_end_date'], '%Y-%m-%d %H:%M:%S')
                query_filters.append(Zbfwb_RemedyBiangeng.StartDate.between(start_date_start, start_date_end))

            if remedybiangengdata.get('EndDate_start_date') not in [None, ''] and remedybiangengdata.get(
                    'EndDate_end_date') not in [None, '']:
                end_date_start = datetime.strptime(remedybiangengdata['EndDate_start_date'], '%Y-%m-%d %H:%M:%S')
                end_date_end = datetime.strptime(remedybiangengdata['EndDate_end_date'], '%Y-%m-%d %H:%M:%S')
                query_filters.append(Zbfwb_RemedyBiangeng.EndDate.between(end_date_start, end_date_end))

            # ----------------------------------------------------------------------------------

            # 执行查询，计算总数
            query = Zbfwb_RemedyBiangeng.query.filter(*query_filters)

            # 分页查询
            pageremedygongdan = query.paginate(page=page, per_page=per_page, error_out=error_out,
                                               max_per_page=max_per_page)

            # 构建分页结果
            result = {
                'has_next': pageremedygongdan.has_next,
                'has_prev': pageremedygongdan.has_prev,
                'data': [remedygongdan.to_dict() for remedygongdan in pageremedygongdan.items],
                'next': pageremedygongdan.next(error_out=False).items if pageremedygongdan.has_next else [],
                'prev': pageremedygongdan.prev(error_out=False).items if pageremedygongdan.has_prev else [],
                'pageNo': pageremedygongdan.page,
                'totalPage': pageremedygongdan.pages,
                'pageSize': pageremedygongdan.per_page,
                'prev_num': pageremedygongdan.prev_num,
                'next_num': pageremedygongdan.next_num,
                'totalCount': pageremedygongdan.total
            }

            # 使用 format_response_page 函数生成返回结果
            response = format_response_page(
                message="翻页查询结果",
                result=result,
                status=200,
                timestamp=datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            )

        except Exception as e:
            # 处理查询过程中可能出现的异常
            response = format_response_page(
                message=f"查询出错: {str(e)}",
                result=None,
                status=201,
                timestamp=datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            )

        return response


# 变更单接口增加 新的图表展示 变更和人名 分别统计数据
#     [ ['product', '数据提取类', '数据修复类', '应用变更类'],
#       ['C-AUDA', 43.3, 85.8, 93.7],
#       ['AIAP', 83.1, 73.4, 55.1],
#       ['BBIS', 86.4, 65.2, 82.5],
#     ]
# 第一个数组 第一个元素为 product 后面元素取值来自于栏位 Categorization_Tier_4
# 从第二个数组开始，每个数组的元素开头取值来自于 ChangeRange，后面 每个统计值与 product 后面的每个类别 Categorization_Tier_4 对应，默认0
@ns_remedy_biangeng.route('/getbymultiqueryappcount')
class RemedyMultiQappconuntMapResource(Resource):

    @ns_remedy_biangeng.expect(remedy_biangeng_multi_query_model)
    # @jwt_required()
    @ns_remedy_biangeng.marshal_with(response_biangeng_app_count_model)
    def post(self):
        """按应用统计变更数量 多值查询"""
        try:
            remedybiangengdata = api.payload

            # 提取查询条件
            query_filters = []

            # 多条件查询逻辑
            if remedybiangengdata.get('TicketID') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.TicketID == remedybiangengdata['TicketID'])

            if remedybiangengdata.get('Change_Request_Status') not in [None, '']:
                try:
                    query_filters.append(
                        Zbfwb_RemedyBiangeng.Change_Request_Status == int(remedybiangengdata['Change_Request_Status']))
                except ValueError:
                    pass

            if remedybiangengdata.get('StatusDescription') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.StatusDescription == remedybiangengdata['StatusDescription'])

            if remedybiangengdata.get('RequestType') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.RequestType == int(remedybiangengdata['RequestType']))
                except ValueError:
                    pass

            if remedybiangengdata.get('CHGName') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.CHGName.ilike(f"%{remedybiangengdata['CHGName']}%"))

            if remedybiangengdata.get('Reason') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Reason.ilike(f"%{remedybiangengdata['Reason']}%"))

            if remedybiangengdata.get('ChangeType') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.ChangeType == int(remedybiangengdata['ChangeType']))
                except ValueError:
                    pass

            if remedybiangengdata.get('ChangeType2') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ChangeType2 == remedybiangengdata['ChangeType2'])

            if remedybiangengdata.get('EvnType') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.EvnType == int(remedybiangengdata['EvnType']))
                except ValueError:
                    pass

            if remedybiangengdata.get('Batch') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Batch == remedybiangengdata['Batch'])

            if remedybiangengdata.get('Categorization_Tier_4') not in [None, '']:
                query_filters.append(
                    Zbfwb_RemedyBiangeng.Categorization_Tier_4 == remedybiangengdata['Categorization_Tier_4'])

            if remedybiangengdata.get('ChangeRange') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ChangeRange == remedybiangengdata['ChangeRange'])

            if remedybiangengdata.get('ImpactMonitor') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.ImpactMonitor == int(remedybiangengdata['ImpactMonitor']))
                except ValueError:
                    pass

            if remedybiangengdata.get('Last_Name') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Last_Name == remedybiangengdata['Last_Name'])

            if remedybiangengdata.get('Department') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Department == remedybiangengdata['Department'])

            if remedybiangengdata.get('ASCHG') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ASCHG == remedybiangengdata['ASCHG'])

            if remedybiangengdata.get('ASGRP') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ASGRP == remedybiangengdata['ASGRP'])
            print("debug1")
            # 处理 StartDate 和 EndDate 范围查询
            if remedybiangengdata.get('StartDate_start_date') and remedybiangengdata.get('StartDate_end_date'):
                try:
                    start_date_start = datetime.strptime(remedybiangengdata['StartDate_start_date'],
                                                         '%Y-%m-%d %H:%M:%S')
                    start_date_end = datetime.strptime(remedybiangengdata['StartDate_end_date'], '%Y-%m-%d %H:%M:%S')
                    query_filters.append(Zbfwb_RemedyBiangeng.StartDate.between(start_date_start, start_date_end))
                except ValueError:
                    pass  # 如果日期格式不正确，忽略此条件

            if remedybiangengdata.get('EndDate_start_date') and remedybiangengdata.get('EndDate_end_date'):
                try:
                    end_date_start = datetime.strptime(remedybiangengdata['EndDate_start_date'], '%Y-%m-%d %H:%M:%S')
                    end_date_end = datetime.strptime(remedybiangengdata['EndDate_end_date'], '%Y-%m-%d %H:%M:%S')
                    query_filters.append(Zbfwb_RemedyBiangeng.EndDate.between(end_date_start, end_date_end))
                except ValueError:
                    pass  # 如果日期格式不正确，忽略此条件
            print("debug2")
            # 执行查询
            results = Zbfwb_RemedyBiangeng.query.filter(*query_filters).all()
            print("debug3")
            # 准备响应数据，按需求统计分类和分组数据
            # categorization_tier_4_values = list(set([result.Categorization_Tier_4 for result in results]))

            # categorization_tier_4_values.sort()
            # 准备响应数据，按需求统计分类和分组数据
            categorization_tier_4_values = list(
                set([result.Categorization_Tier_4 for result in results if result.Categorization_Tier_4 is not None]))
            print("debug6")
            categorization_tier_4_values.sort()
            print("debug7")
            # data = [['product'] + categorization_tier_4_values]
            data = [['ChangeRange'] + categorization_tier_4_values]
            print("debug4")
            for change_range in set(result.ChangeRange for result in results if result.ChangeRange):
                row = [change_range]
                for category in categorization_tier_4_values:
                    count = sum(1 for result in results if
                                result.ChangeRange == change_range and result.Categorization_Tier_4 == category)
                    row.append(count)
                data.append(row)
            print("debug5")
            # 准备最终响应数据
            response_data = {
                "message": {
                    "result": 1,  # 查询成功
                    "result_text": "查询成功",
                    "count": len(results),
                    "data": data
                }
            }

            return response_data, 200

        except Exception as e:
            # 捕获异常并准备异常响应数据
            response_data = {
                "message": {
                    "result": 0,  # 查询异常
                    "result_text": f"查询异常: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }

            # 返回封装好的异常响应模型，状态码201
            return response_data, 201


# 按人名统计变更数量 单值查询
@ns_remedy_biangeng.route('/getbymultiquerystaffcount')
class RemedyMultiQstaffconuntMapResource(Resource):

    @ns_remedy_biangeng.expect(remedy_biangeng_multi_query_model)
    # @jwt_required()
    @ns_remedy_biangeng.marshal_with(response_biangeng_staff_count_model)
    def post(self):
        """ 根据人名统计变更数量，单值查询"""
        try:

            remedybiangengdata = api.payload

            # 提取查询条件
            query_filters = []

            # 多条件查询逻辑
            if remedybiangengdata.get('TicketID') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.TicketID == remedybiangengdata['TicketID'])

            if remedybiangengdata.get('Change_Request_Status') not in [None, '']:
                try:
                    query_filters.append(
                        Zbfwb_RemedyBiangeng.Change_Request_Status == int(remedybiangengdata['Change_Request_Status']))
                except ValueError:
                    pass

            if remedybiangengdata.get('StatusDescription') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.StatusDescription == remedybiangengdata['StatusDescription'])

            if remedybiangengdata.get('RequestType') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.RequestType == int(remedybiangengdata['RequestType']))
                except ValueError:
                    pass

            if remedybiangengdata.get('CHGName') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.CHGName.ilike(f"%{remedybiangengdata['CHGName']}%"))

            if remedybiangengdata.get('Reason') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Reason.ilike(f"%{remedybiangengdata['Reason']}%"))

            if remedybiangengdata.get('ChangeType') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.ChangeType == int(remedybiangengdata['ChangeType']))
                except ValueError:
                    pass

            if remedybiangengdata.get('ChangeType2') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ChangeType2 == remedybiangengdata['ChangeType2'])

            if remedybiangengdata.get('EvnType') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.EvnType == int(remedybiangengdata['EvnType']))
                except ValueError:
                    pass

            if remedybiangengdata.get('Batch') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Batch == remedybiangengdata['Batch'])

            if remedybiangengdata.get('Categorization_Tier_4') not in [None, '']:
                query_filters.append(
                    Zbfwb_RemedyBiangeng.Categorization_Tier_4 == remedybiangengdata['Categorization_Tier_4'])

            if remedybiangengdata.get('ChangeRange') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ChangeRange == remedybiangengdata['ChangeRange'])

            if remedybiangengdata.get('ImpactMonitor') not in [None, '']:
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.ImpactMonitor == int(remedybiangengdata['ImpactMonitor']))
                except ValueError:
                    pass

            if remedybiangengdata.get('Last_Name') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Last_Name == remedybiangengdata['Last_Name'])

            if remedybiangengdata.get('Department') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.Department == remedybiangengdata['Department'])

            if remedybiangengdata.get('ASCHG') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ASCHG == remedybiangengdata['ASCHG'])

            if remedybiangengdata.get('ASGRP') not in [None, '']:
                query_filters.append(Zbfwb_RemedyBiangeng.ASGRP == remedybiangengdata['ASGRP'])

            # 处理 StartDate 和 EndDate 范围查询
            if remedybiangengdata.get('StartDate_start_date') and remedybiangengdata.get('StartDate_end_date'):
                try:
                    start_date_start = datetime.strptime(remedybiangengdata['StartDate_start_date'],
                                                         '%Y-%m-%d %H:%M:%S')
                    start_date_end = datetime.strptime(remedybiangengdata['StartDate_end_date'], '%Y-%m-%d %H:%M:%S')
                    query_filters.append(Zbfwb_RemedyBiangeng.StartDate.between(start_date_start, start_date_end))
                except ValueError:
                    pass  # 如果日期格式不正确，忽略此条件

            if remedybiangengdata.get('EndDate_start_date') and remedybiangengdata.get('EndDate_end_date'):
                try:
                    end_date_start = datetime.strptime(remedybiangengdata['EndDate_start_date'], '%Y-%m-%d %H:%M:%S')
                    end_date_end = datetime.strptime(remedybiangengdata['EndDate_end_date'], '%Y-%m-%d %H:%M:%S')
                    query_filters.append(Zbfwb_RemedyBiangeng.EndDate.between(end_date_start, end_date_end))
                except ValueError:
                    pass  # 如果日期格式不正确，忽略此条件

            # 执行查询
            results = Zbfwb_RemedyBiangeng.query.filter(*query_filters).all()

            # 准备 Categorization_Tier_4 和 ASCHG 的唯一值集合
            categorization_tier_4_values = list(
                set([result.Categorization_Tier_4 for result in results if result.Categorization_Tier_4 is not None]))
            categorization_tier_4_values.sort()

            aschg_values = list(set([result.ASCHG for result in results if result.ASCHG is not None]))
            aschg_values.sort()

            # 准备数据结构
            # data = [['product'] + categorization_tier_4_values]
            data = [['ASCHG'] + categorization_tier_4_values]

            for aschg in aschg_values:
                row = [aschg]
                for category in categorization_tier_4_values:
                    count = sum(
                        1 for result in results if result.ASCHG == aschg and result.Categorization_Tier_4 == category)
                    row.append(count)
                data.append(row)

            # 准备最终响应数据
            response_data = {
                "message": {
                    "result": 1,  # 查询成功
                    "result_text": "查询成功",
                    "count": len(results),
                    "data": data
                }
            }

            return response_data, 200

        except Exception as e:
            # 捕获异常并准备异常响应数据
            response_data = {
                "message": {
                    "result": 0,  # 查询异常
                    "result_text": f"查询异常: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }

            # 返回封装好的异常响应模型，状态码201
            return response_data, 201


# 变更单接口增加 新的图表展示 变更和人名 分别统计数据
#     [ ['product', '数据提取类', '数据修复类', '应用变更类'],
#       ['C-AUDA', 43.3, 85.8, 93.7],
#       ['AIAP', 83.1, 73.4, 55.1],
#       ['BBIS', 86.4, 65.2, 82.5],
#     ]
# 第一个数组 第一个元素为 product 后面元素取值来自于栏位 Categorization_Tier_4
# 从第二个数组开始，每个数组的元素开头取值来自于 ChangeRange，后面 每个统计值与 product 后面的每个类别 Categorization_Tier_4 对应，默认0

# 主要改动：
# remedy_biangeng_multi_query_group_model:
#
# 将所有字段的类型由单一的 fields.String 改为 fields.List(fields.String)，这表示每个条件现在接受一个字符串列表。
# 查询逻辑:
#
# 使用 .in_() 代替 == 来处理列表中的多个值。
# 处理日期范围时保留了原来的逻辑，但需要检查日期字段的格式。
# 优化:
#
# 避免了多个 if 判断的重复结构，通过统一处理列表查询条件提高代码可读性。
# 对 ilike 操作进行了稍微改进，支持多个查询值时使用 '%'.join() 拼接。
# 数据处理:
#
# 在处理响应数据时，依然使用了按类别（如 Categorization_Tier_4）分组统计的逻辑，并且生成了一个二维表格。
@ns_remedy_biangeng.route('/getbymultiqueryappmcount')
class RemedyMultiQappmconuntMapResource(Resource):

    @ns_remedy_biangeng.expect(remedy_biangeng_multi_query_group_model)
    @ns_remedy_biangeng.marshal_with(response_biangeng_app_count_model)
    def post(self):
        """按应用统计变更数量 单值查询"""
        try:
            # """按应用统计变更数量"""
            remedybiangengdata = api.payload

            # 提取查询条件
            query_filters = []

            # 多条件查询逻辑
            if remedybiangengdata.get('TicketID'):
                query_filters.append(Zbfwb_RemedyBiangeng.TicketID.in_(remedybiangengdata['TicketID']))

            if remedybiangengdata.get('Change_Request_Status'):
                try:
                    query_filters.append(
                        Zbfwb_RemedyBiangeng.Change_Request_Status.in_(
                            [int(status) for status in remedybiangengdata['Change_Request_Status']])
                    )
                except ValueError:
                    pass  # 如果转换失败，忽略该条件

            if remedybiangengdata.get('StatusDescription'):
                query_filters.append(
                    Zbfwb_RemedyBiangeng.StatusDescription.in_(remedybiangengdata['StatusDescription']))

            if remedybiangengdata.get('RequestType'):
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.RequestType.in_(
                        [int(request) for request in remedybiangengdata['RequestType']]))
                except ValueError:
                    pass

            if remedybiangengdata.get('CHGName'):
                query_filters.append(Zbfwb_RemedyBiangeng.CHGName.ilike(f"%{'%'.join(remedybiangengdata['CHGName'])}%"))

            if remedybiangengdata.get('Reason'):
                query_filters.append(Zbfwb_RemedyBiangeng.Reason.ilike(f"%{'%'.join(remedybiangengdata['Reason'])}%"))

            if remedybiangengdata.get('ChangeType'):
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.ChangeType.in_(
                        [int(change_type) for change_type in remedybiangengdata['ChangeType']]))
                except ValueError:
                    pass

            if remedybiangengdata.get('ChangeType2'):
                query_filters.append(Zbfwb_RemedyBiangeng.ChangeType2.in_(remedybiangengdata['ChangeType2']))

            if remedybiangengdata.get('EvnType'):
                try:
                    query_filters.append(
                        Zbfwb_RemedyBiangeng.EvnType.in_([int(evntype) for evntype in remedybiangengdata['EvnType']]))
                except ValueError:
                    pass

            if remedybiangengdata.get('Batch'):
                query_filters.append(Zbfwb_RemedyBiangeng.Batch.in_(remedybiangengdata['Batch']))

            if remedybiangengdata.get('Categorization_Tier_4'):
                query_filters.append(
                    Zbfwb_RemedyBiangeng.Categorization_Tier_4.in_(remedybiangengdata['Categorization_Tier_4']))

            if remedybiangengdata.get('ChangeRange'):
                query_filters.append(Zbfwb_RemedyBiangeng.ChangeRange.in_(remedybiangengdata['ChangeRange']))

            if remedybiangengdata.get('ImpactMonitor'):
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.ImpactMonitor.in_(
                        [int(impact) for impact in remedybiangengdata['ImpactMonitor']]))
                except ValueError:
                    pass

            if remedybiangengdata.get('Last_Name'):
                query_filters.append(Zbfwb_RemedyBiangeng.Last_Name.in_(remedybiangengdata['Last_Name']))

            if remedybiangengdata.get('Department'):
                query_filters.append(Zbfwb_RemedyBiangeng.Department.in_(remedybiangengdata['Department']))

            if remedybiangengdata.get('ASCHG'):
                query_filters.append(Zbfwb_RemedyBiangeng.ASCHG.in_(remedybiangengdata['ASCHG']))

            if remedybiangengdata.get('ASGRP'):
                query_filters.append(Zbfwb_RemedyBiangeng.ASGRP.in_(remedybiangengdata['ASGRP']))

            # 处理 StartDate 和 EndDate 范围查询
            if remedybiangengdata.get('StartDate_start_date') and remedybiangengdata.get('StartDate_end_date'):
                try:
                    start_date_start = datetime.strptime(remedybiangengdata['StartDate_start_date'],
                                                         '%Y-%m-%d %H:%M:%S')
                    start_date_end = datetime.strptime(remedybiangengdata['StartDate_end_date'], '%Y-%m-%d %H:%M:%S')
                    query_filters.append(Zbfwb_RemedyBiangeng.StartDate.between(start_date_start, start_date_end))
                except ValueError:
                    pass  # 如果日期格式不正确，忽略此条件

            if remedybiangengdata.get('EndDate_start_date') and remedybiangengdata.get('EndDate_end_date'):
                try:
                    end_date_start = datetime.strptime(remedybiangengdata['EndDate_start_date'], '%Y-%m-%d %H:%M:%S')
                    end_date_end = datetime.strptime(remedybiangengdata['EndDate_end_date'], '%Y-%m-%d %H:%M:%S')
                    query_filters.append(Zbfwb_RemedyBiangeng.EndDate.between(end_date_start, end_date_end))
                except ValueError:
                    pass  # 如果日期格式不正确，忽略此条件

            # 执行查询
            results = Zbfwb_RemedyBiangeng.query.filter(*query_filters).all()

            # 准备响应数据，按需求统计分类和分组数据
            categorization_tier_4_values = list(
                set([result.Categorization_Tier_4 for result in results if result.Categorization_Tier_4 is not None]))
            categorization_tier_4_values.sort()

            # data = [['product'] + categorization_tier_4_values]
            data = [['ChangeRange'] + categorization_tier_4_values]

            for change_range in set(result.ChangeRange for result in results if result.ChangeRange):
                row = [change_range]
                for category in categorization_tier_4_values:
                    count = sum(1 for result in results if
                                result.ChangeRange == change_range and result.Categorization_Tier_4 == category)
                    row.append(count)
                data.append(row)

            # 准备最终响应数据
            response_data = {
                "message": {
                    "result": 1,  # 查询成功
                    "result_text": "查询成功",
                    "count": len(results),
                    "data": data
                }
            }

            return response_data, 200

        except Exception as e:
            # 捕获异常并准备异常响应数据
            response_data = {
                "message": {
                    "result": 0,  # 查询异常
                    "result_text": f"查询异常: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }

            # 返回封装好的异常响应模型，状态码201
            return response_data, 201


# 主要变化：
# 处理列表输入：查询时使用 in_() 来支持列表类型字段的查询，代替了之前的 == 查询方式。这样就能处理输入为多个选项的情况。
# 转换列表中的值：对于可能为数字的字段（例如 Change_Request_Status、RequestType、ImpactMonitor 等），
# 我使用了 int() 转换来确保能够正确处理这些值。
# 更新查询逻辑：每个字段如果是一个列表（如 TicketID、CHGName 等），就进行 in_() 查询，来查找匹配的多个值。
# 按人名统计变更数量 单值查询
@ns_remedy_biangeng.route('/getbymultiquerystaffmcount')
class RemedyMultiQstaffmconuntMapResource(Resource):

    @ns_remedy_biangeng.expect(remedy_biangeng_multi_query_group_model)
    # @jwt_required()
    @ns_remedy_biangeng.marshal_with(response_biangeng_staff_count_model)
    def post(self):
        """ 根据人名统计变更数量，多值查询"""
        try:
            remedybiangengdata = api.payload

            # 提取查询条件
            query_filters = []

            # 多条件查询逻辑，适配列表类型的输入
            if remedybiangengdata.get('TicketID'):
                query_filters.append(Zbfwb_RemedyBiangeng.TicketID.in_(remedybiangengdata['TicketID']))

            if remedybiangengdata.get('Change_Request_Status'):
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.Change_Request_Status.in_(
                        [int(status) for status in remedybiangengdata['Change_Request_Status']]))
                except ValueError:
                    pass

            if remedybiangengdata.get('StatusDescription'):
                query_filters.append(
                    Zbfwb_RemedyBiangeng.StatusDescription.in_(remedybiangengdata['StatusDescription']))

            if remedybiangengdata.get('RequestType'):
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.RequestType.in_(
                        [int(request_type) for request_type in remedybiangengdata['RequestType']]))
                except ValueError:
                    pass

            if remedybiangengdata.get('CHGName'):
                query_filters.append(Zbfwb_RemedyBiangeng.CHGName.ilike(f"%{remedybiangengdata['CHGName']}%"))

            if remedybiangengdata.get('Reason'):
                query_filters.append(Zbfwb_RemedyBiangeng.Reason.ilike(f"%{remedybiangengdata['Reason']}%"))

            if remedybiangengdata.get('ChangeType'):
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.ChangeType.in_(
                        [int(change_type) for change_type in remedybiangengdata['ChangeType']]))
                except ValueError:
                    pass

            if remedybiangengdata.get('ChangeType2'):
                query_filters.append(Zbfwb_RemedyBiangeng.ChangeType2.in_(remedybiangengdata['ChangeType2']))

            if remedybiangengdata.get('EvnType'):
                try:
                    query_filters.append(
                        Zbfwb_RemedyBiangeng.EvnType.in_([int(evntype) for evntype in remedybiangengdata['EvnType']]))
                except ValueError:
                    pass

            if remedybiangengdata.get('Batch'):
                query_filters.append(Zbfwb_RemedyBiangeng.Batch.in_(remedybiangengdata['Batch']))

            if remedybiangengdata.get('Categorization_Tier_4'):
                query_filters.append(
                    Zbfwb_RemedyBiangeng.Categorization_Tier_4.in_(remedybiangengdata['Categorization_Tier_4']))

            if remedybiangengdata.get('ChangeRange'):
                query_filters.append(Zbfwb_RemedyBiangeng.ChangeRange.in_(remedybiangengdata['ChangeRange']))

            if remedybiangengdata.get('ImpactMonitor'):
                try:
                    query_filters.append(Zbfwb_RemedyBiangeng.ImpactMonitor.in_(
                        [int(impact_monitor) for impact_monitor in remedybiangengdata['ImpactMonitor']]))
                except ValueError:
                    pass

            if remedybiangengdata.get('Last_Name'):
                query_filters.append(Zbfwb_RemedyBiangeng.Last_Name.in_(remedybiangengdata['Last_Name']))

            if remedybiangengdata.get('Department'):
                query_filters.append(Zbfwb_RemedyBiangeng.Department.in_(remedybiangengdata['Department']))

            if remedybiangengdata.get('ASCHG'):
                query_filters.append(Zbfwb_RemedyBiangeng.ASCHG.in_(remedybiangengdata['ASCHG']))

            if remedybiangengdata.get('ASGRP'):
                query_filters.append(Zbfwb_RemedyBiangeng.ASGRP.in_(remedybiangengdata['ASGRP']))

            # 处理 StartDate 和 EndDate 范围查询
            if remedybiangengdata.get('StartDate_start_date') and remedybiangengdata.get('StartDate_end_date'):
                try:
                    start_date_start = datetime.strptime(remedybiangengdata['StartDate_start_date'],
                                                         '%Y-%m-%d %H:%M:%S')
                    start_date_end = datetime.strptime(remedybiangengdata['StartDate_end_date'], '%Y-%m-%d %H:%M:%S')
                    query_filters.append(Zbfwb_RemedyBiangeng.StartDate.between(start_date_start, start_date_end))
                except ValueError:
                    pass  # 如果日期格式不正确，忽略此条件

            if remedybiangengdata.get('EndDate_start_date') and remedybiangengdata.get('EndDate_end_date'):
                try:
                    end_date_start = datetime.strptime(remedybiangengdata['EndDate_start_date'], '%Y-%m-%d %H:%M:%S')
                    end_date_end = datetime.strptime(remedybiangengdata['EndDate_end_date'], '%Y-%m-%d %H:%M:%S')
                    query_filters.append(Zbfwb_RemedyBiangeng.EndDate.between(end_date_start, end_date_end))
                except ValueError:
                    pass  # 如果日期格式不正确，忽略此条件

            # 执行查询
            results = Zbfwb_RemedyBiangeng.query.filter(*query_filters).all()

            # 准备 Categorization_Tier_4 和 ASCHG 的唯一值集合
            categorization_tier_4_values = list(
                set([result.Categorization_Tier_4 for result in results if result.Categorization_Tier_4 is not None]))
            categorization_tier_4_values.sort()

            aschg_values = list(set([result.ASCHG for result in results if result.ASCHG is not None]))
            aschg_values.sort()

            # 准备数据结构
            # data = [['product'] + categorization_tier_4_values]
            data = [['ASCHG'] + categorization_tier_4_values]

            for aschg in aschg_values:
                row = [aschg]
                for category in categorization_tier_4_values:
                    count = sum(
                        1 for result in results if result.ASCHG == aschg and result.Categorization_Tier_4 == category)
                    row.append(count)
                data.append(row)

            # 准备最终响应数据
            response_data = {
                "message": {
                    "result": 1,  # 查询成功
                    "result_text": "查询成功",
                    "count": len(results),
                    "data": data
                }
            }

            return response_data, 200

        except Exception as e:
            # 捕获异常并准备异常响应数据
            response_data = {
                "message": {
                    "result": 0,  # 查询异常
                    "result_text": f"查询异常: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }

            # 返回封装好的异常响应模型，状态码201
            return response_data, 201


@ns_remedy_biangeng.route('/remedygroupbiangengpage')
class RemedyGroupBiangengPageResource(Resource):
    @ns_remedy_biangeng.marshal_with(response_biangeng_page_data_model)
    @ns_remedy_biangeng.expect(remedy_biangeng_multi_query_page_group_model)
    def post(self):
        """分页获取变更单列表（支持多值查询）"""
        remedybiangengdata = api.payload

        try:
            # 获取分页请求信息
            page = remedybiangengdata['pageNo']
            if page < 1:
                page = 1

            per_page = remedybiangengdata['pageSize']
            if per_page < 1:
                per_page = 10

            max_per_page = remedybiangengdata['max_page']
            error_out = False

            # ----------------------------------------------------------------------------------

            # 提取查询条件
            query_filters = []

            def add_filter(field_name, query_field, fuzzy=False):
                """辅助函数，处理多值或模糊查询"""
                values = remedybiangengdata.get(field_name, [])
                if values:
                    if fuzzy:
                        query_filters.extend(
                            [query_field.ilike(f"%{value}%") for value in values if value not in [None, '']]
                        )
                    else:
                        query_filters.append(query_field.in_(values))

            # 多条件查询逻辑
            add_filter('TicketID', Zbfwb_RemedyBiangeng.TicketID)
            add_filter('Change_Request_Status', Zbfwb_RemedyBiangeng.Change_Request_Status)
            add_filter('StatusDescription', Zbfwb_RemedyBiangeng.StatusDescription)
            add_filter('RequestType', Zbfwb_RemedyBiangeng.RequestType)
            add_filter('CHGName', Zbfwb_RemedyBiangeng.CHGName, fuzzy=True)
            add_filter('Reason', Zbfwb_RemedyBiangeng.Reason, fuzzy=True)
            add_filter('ChangeType', Zbfwb_RemedyBiangeng.ChangeType)
            add_filter('ChangeType2', Zbfwb_RemedyBiangeng.ChangeType2)
            add_filter('EvnType', Zbfwb_RemedyBiangeng.EvnType)
            add_filter('Batch', Zbfwb_RemedyBiangeng.Batch)
            add_filter('Categorization_Tier_4', Zbfwb_RemedyBiangeng.Categorization_Tier_4)
            add_filter('ChangeRange', Zbfwb_RemedyBiangeng.ChangeRange)
            add_filter('ImpactMonitor', Zbfwb_RemedyBiangeng.ImpactMonitor)
            add_filter('Last_Name', Zbfwb_RemedyBiangeng.Last_Name)
            add_filter('Department', Zbfwb_RemedyBiangeng.Department)
            add_filter('ASCHG', Zbfwb_RemedyBiangeng.ASCHG)
            add_filter('ASGRP', Zbfwb_RemedyBiangeng.ASGRP)

            # 处理 StartDate 和 EndDate 范围查询
            if remedybiangengdata.get('StartDate_start_date') not in [None, ''] and remedybiangengdata.get(
                    'StartDate_end_date') not in [None, '']:
                start_date_start = datetime.strptime(remedybiangengdata['StartDate_start_date'], '%Y-%m-%d %H:%M:%S')
                start_date_end = datetime.strptime(remedybiangengdata['StartDate_end_date'], '%Y-%m-%d %H:%M:%S')
                query_filters.append(Zbfwb_RemedyBiangeng.StartDate.between(start_date_start, start_date_end))

            if remedybiangengdata.get('EndDate_start_date') not in [None, ''] and remedybiangengdata.get(
                    'EndDate_end_date') not in [None, '']:
                end_date_start = datetime.strptime(remedybiangengdata['EndDate_start_date'], '%Y-%m-%d %H:%M:%S')
                end_date_end = datetime.strptime(remedybiangengdata['EndDate_end_date'], '%Y-%m-%d %H:%M:%S')
                query_filters.append(Zbfwb_RemedyBiangeng.EndDate.between(end_date_start, end_date_end))

            # ----------------------------------------------------------------------------------

            # 执行查询，计算总数
            query = Zbfwb_RemedyBiangeng.query.filter(*query_filters)

            # 分页查询
            pageremedygongdan = query.paginate(page=page, per_page=per_page, error_out=error_out,
                                               max_per_page=max_per_page)

            # 构建分页结果
            result = {
                'has_next': pageremedygongdan.has_next,
                'has_prev': pageremedygongdan.has_prev,
                'data': [remedygongdan.to_dict() for remedygongdan in pageremedygongdan.items],
                'next': pageremedygongdan.next(error_out=False).items if pageremedygongdan.has_next else [],
                'prev': pageremedygongdan.prev(error_out=False).items if pageremedygongdan.has_prev else [],
                'pageNo': pageremedygongdan.page,
                'totalPage': pageremedygongdan.pages,
                'pageSize': pageremedygongdan.per_page,
                'prev_num': pageremedygongdan.prev_num,
                'next_num': pageremedygongdan.next_num,
                'totalCount': pageremedygongdan.total
            }

            # 使用 format_response_page 函数生成返回结果
            response = format_response_page(
                message="翻页查询结果",
                result=result,
                status=200,
                timestamp=datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            )

        except Exception as e:
            # 处理查询过程中可能出现的异常
            response = format_response_page(
                message=f"查询出错: {str(e)}",
                result=None,
                status=201,
                timestamp=datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            )

        return response


@ns_remedy_biangeng.route('/remedygroupbiangengpageaddid')
class RemedyGroupBiangengPageAddIdResource(Resource):
    @ns_remedy_biangeng.marshal_with(response_biangeng_page_data_id_model)
    @ns_remedy_biangeng.expect(remedy_biangeng_multi_query_page_group_model)
    def post(self):
        """分页获取变更单列表（支持多值查询,输出增加id序号）"""
        remedybiangengdata = api.payload

        try:
            # 获取分页请求信息
            page = remedybiangengdata['pageNo']
            if page < 1:
                page = 1

            per_page = remedybiangengdata['pageSize']
            if per_page < 1:
                per_page = 10

            max_per_page = remedybiangengdata['max_page']
            error_out = False

            # ----------------------------------------------------------------------------------
            # 提取查询条件
            query_filters = []

            def add_filter(field_name, query_field, fuzzy=False):
                """辅助函数，处理多值或模糊查询"""
                values = remedybiangengdata.get(field_name, [])
                if values:
                    if fuzzy:
                        query_filters.extend(
                            [query_field.ilike(f"%{value}%") for value in values if value not in [None, '']]
                        )
                    else:
                        query_filters.append(query_field.in_(values))

            # 多条件查询逻辑
            add_filter('TicketID', Zbfwb_RemedyBiangeng.TicketID)
            add_filter('Change_Request_Status', Zbfwb_RemedyBiangeng.Change_Request_Status)
            add_filter('StatusDescription', Zbfwb_RemedyBiangeng.StatusDescription)
            add_filter('RequestType', Zbfwb_RemedyBiangeng.RequestType)
            add_filter('CHGName', Zbfwb_RemedyBiangeng.CHGName, fuzzy=True)
            add_filter('Reason', Zbfwb_RemedyBiangeng.Reason, fuzzy=True)
            add_filter('ChangeType', Zbfwb_RemedyBiangeng.ChangeType)
            add_filter('ChangeType2', Zbfwb_RemedyBiangeng.ChangeType2)
            add_filter('EvnType', Zbfwb_RemedyBiangeng.EvnType)
            add_filter('Batch', Zbfwb_RemedyBiangeng.Batch)
            add_filter('Categorization_Tier_4', Zbfwb_RemedyBiangeng.Categorization_Tier_4)
            add_filter('ChangeRange', Zbfwb_RemedyBiangeng.ChangeRange)
            add_filter('ImpactMonitor', Zbfwb_RemedyBiangeng.ImpactMonitor)
            add_filter('Last_Name', Zbfwb_RemedyBiangeng.Last_Name)
            add_filter('Department', Zbfwb_RemedyBiangeng.Department)
            add_filter('ASCHG', Zbfwb_RemedyBiangeng.ASCHG)
            add_filter('ASGRP', Zbfwb_RemedyBiangeng.ASGRP)

            # 处理 StartDate 和 EndDate 范围查询
            if remedybiangengdata.get('StartDate_start_date') not in [None, ''] and remedybiangengdata.get(
                    'StartDate_end_date') not in [None, '']:
                start_date_start = datetime.strptime(remedybiangengdata['StartDate_start_date'], '%Y-%m-%d %H:%M:%S')
                start_date_end = datetime.strptime(remedybiangengdata['StartDate_end_date'], '%Y-%m-%d %H:%M:%S')
                query_filters.append(Zbfwb_RemedyBiangeng.StartDate.between(start_date_start, start_date_end))

            if remedybiangengdata.get('EndDate_start_date') not in [None, ''] and remedybiangengdata.get(
                    'EndDate_end_date') not in [None, '']:
                end_date_start = datetime.strptime(remedybiangengdata['EndDate_start_date'], '%Y-%m-%d %H:%M:%S')
                end_date_end = datetime.strptime(remedybiangengdata['EndDate_end_date'], '%Y-%m-%d %H:%M:%S')
                query_filters.append(Zbfwb_RemedyBiangeng.EndDate.between(end_date_start, end_date_end))

            # ----------------------------------------------------------------------------------
            # 执行查询，计算总数
            query = Zbfwb_RemedyBiangeng.query.filter(*query_filters)

            # 分页查询
            pageremedygongdan = query.paginate(page=page, per_page=per_page, error_out=error_out,
                                               max_per_page=max_per_page)

            # 构建分页结果，增加序号字段
            start_id = (page - 1) * per_page + 1  # 计算当前页第一个序号
            data_with_id = []
            for idx, remedygongdan in enumerate(pageremedygongdan.items, start=start_id):
                row = remedygongdan.to_dict()
                row['id'] = idx  # 添加序号字段
                data_with_id.append(row)

            result = {
                'has_next': pageremedygongdan.has_next,
                'has_prev': pageremedygongdan.has_prev,
                'data': data_with_id,
                'next': [
                    {**item.to_dict(), 'id': idx + len(data_with_id)}
                    for idx, item in enumerate(pageremedygongdan.next(error_out=False).items)
                ] if pageremedygongdan.has_next else [],
                'prev': [
                    {**item.to_dict(), 'id': idx + len(data_with_id)}
                    for idx, item in enumerate(pageremedygongdan.prev(error_out=False).items)
                ] if pageremedygongdan.has_prev else [],
                'pageNo': pageremedygongdan.page,
                'totalPage': pageremedygongdan.pages,
                'pageSize': pageremedygongdan.per_page,
                'prev_num': pageremedygongdan.prev_num,
                'next_num': pageremedygongdan.next_num,
                'totalCount': pageremedygongdan.total
            }

            # 使用 format_response_page 函数生成返回结果
            response = format_response_page(
                message="翻页查询结果",
                result=result,
                status=200,
                timestamp=datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            )

        except Exception as e:
            # 处理查询过程中可能出现的异常
            response = format_response_page(
                message=f"查询出错: {str(e)}",
                result=None,
                status=201,
                timestamp=datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            )

        return response
